{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "np.random.seed(12345)\n",
    "plt.rc(\"figure\", figsize=(10, 6))\n",
    "pd.options.display.max_colwidth = 75\n",
    "pd.options.display.max_columns = 20\n",
    "pd.options.display.max_rows = 10\n",
    "np.set_printoptions(precision=4, suppress=True)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:15.988315Z",
     "end_time": "2024-04-18T09:37:16.808260Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 6.1 读写文本格式的数据"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "outputs": [
    {
     "data": {
      "text/plain": "   a   b   c   d message\n0  1   2   3   4   hello\n1  5   6   7   8   world\n2  9  10  11  12     foo",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>message</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>4</td>\n      <td>hello</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>5</td>\n      <td>6</td>\n      <td>7</td>\n      <td>8</td>\n      <td>world</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>9</td>\n      <td>10</td>\n      <td>11</td>\n      <td>12</td>\n      <td>foo</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('examples/ex1.csv')\n",
    "df"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:16.810261Z",
     "end_time": "2024-04-18T09:37:16.825692Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "outputs": [
    {
     "data": {
      "text/plain": "   a   b   c   d message\n0  1   2   3   4   hello\n1  5   6   7   8   world\n2  9  10  11  12     foo",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>message</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>4</td>\n      <td>hello</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>5</td>\n      <td>6</td>\n      <td>7</td>\n      <td>8</td>\n      <td>world</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>9</td>\n      <td>10</td>\n      <td>11</td>\n      <td>12</td>\n      <td>foo</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_table('examples/ex1.csv', sep=',')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:16.823686Z",
     "end_time": "2024-04-18T09:37:16.832937Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "outputs": [
    {
     "data": {
      "text/plain": "   0   1   2   3      4\n0  1   2   3   4  hello\n1  5   6   7   8  world\n2  9  10  11  12    foo",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>0</th>\n      <th>1</th>\n      <th>2</th>\n      <th>3</th>\n      <th>4</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>4</td>\n      <td>hello</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>5</td>\n      <td>6</td>\n      <td>7</td>\n      <td>8</td>\n      <td>world</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>9</td>\n      <td>10</td>\n      <td>11</td>\n      <td>12</td>\n      <td>foo</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('examples/ex2.csv', header=None)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:16.830935Z",
     "end_time": "2024-04-18T09:37:16.841851Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "outputs": [
    {
     "data": {
      "text/plain": "   a   b   c   d message\n0  1   2   3   4   hello\n1  5   6   7   8   world\n2  9  10  11  12     foo",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>message</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>4</td>\n      <td>hello</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>5</td>\n      <td>6</td>\n      <td>7</td>\n      <td>8</td>\n      <td>world</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>9</td>\n      <td>10</td>\n      <td>11</td>\n      <td>12</td>\n      <td>foo</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "names = ['a', 'b', 'c', 'd', 'message']\n",
    "pd.read_csv('examples/ex2.csv', names=names)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:16.841851Z",
     "end_time": "2024-04-18T09:37:16.894173Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "outputs": [
    {
     "data": {
      "text/plain": "         a   b   c   d\nmessage               \nhello    1   2   3   4\nworld    5   6   7   8\nfoo      9  10  11  12",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n    </tr>\n    <tr>\n      <th>message</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>hello</th>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>world</th>\n      <td>5</td>\n      <td>6</td>\n      <td>7</td>\n      <td>8</td>\n    </tr>\n    <tr>\n      <th>foo</th>\n      <td>9</td>\n      <td>10</td>\n      <td>11</td>\n      <td>12</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('examples/ex2.csv', names=names, index_col='message')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:16.851653Z",
     "end_time": "2024-04-18T09:37:16.894173Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "outputs": [
    {
     "data": {
      "text/plain": "           value1  value2\nkey1 key2                \none  a          1       2\n     b          3       4\n     c          5       6\n     d          7       8\ntwo  a          9      10\n     b         11      12\n     c         13      14\n     d         15      16",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th></th>\n      <th>value1</th>\n      <th>value2</th>\n    </tr>\n    <tr>\n      <th>key1</th>\n      <th>key2</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"4\" valign=\"top\">one</th>\n      <th>a</th>\n      <td>1</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>b</th>\n      <td>3</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>c</th>\n      <td>5</td>\n      <td>6</td>\n    </tr>\n    <tr>\n      <th>d</th>\n      <td>7</td>\n      <td>8</td>\n    </tr>\n    <tr>\n      <th rowspan=\"4\" valign=\"top\">two</th>\n      <th>a</th>\n      <td>9</td>\n      <td>10</td>\n    </tr>\n    <tr>\n      <th>b</th>\n      <td>11</td>\n      <td>12</td>\n    </tr>\n    <tr>\n      <th>c</th>\n      <td>13</td>\n      <td>14</td>\n    </tr>\n    <tr>\n      <th>d</th>\n      <td>15</td>\n      <td>16</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "parsed = pd.read_csv('examples/csv_mindex.csv', index_col=['key1', 'key2'])\n",
    "parsed"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:16.861759Z",
     "end_time": "2024-04-18T09:37:17.013591Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "outputs": [
    {
     "data": {
      "text/plain": "['            A         B         C\\n',\n 'aaa -0.264438 -1.026059 -0.619500\\n',\n 'bbb  0.927272  0.302904 -0.032399\\n',\n 'ccc -0.264273 -0.386314 -0.217601\\n',\n 'ddd -0.871858 -0.348382  1.100491\\n']"
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(open('examples/ex3.txt'))"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:16.870818Z",
     "end_time": "2024-04-18T09:37:17.035596Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "outputs": [
    {
     "data": {
      "text/plain": "            A         B         C\naaa -0.264438 -1.026059 -0.619500\nbbb  0.927272  0.302904 -0.032399\nccc -0.264273 -0.386314 -0.217601\nddd -0.871858 -0.348382  1.100491",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>A</th>\n      <th>B</th>\n      <th>C</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>aaa</th>\n      <td>-0.264438</td>\n      <td>-1.026059</td>\n      <td>-0.619500</td>\n    </tr>\n    <tr>\n      <th>bbb</th>\n      <td>0.927272</td>\n      <td>0.302904</td>\n      <td>-0.032399</td>\n    </tr>\n    <tr>\n      <th>ccc</th>\n      <td>-0.264273</td>\n      <td>-0.386314</td>\n      <td>-0.217601</td>\n    </tr>\n    <tr>\n      <th>ddd</th>\n      <td>-0.871858</td>\n      <td>-0.348382</td>\n      <td>1.100491</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = pd.read_table('examples/ex3.txt', sep='\\s+')\n",
    "result"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:16.875166Z",
     "end_time": "2024-04-18T09:37:17.035596Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "outputs": [
    {
     "data": {
      "text/plain": "   a   b   c   d message\n0  1   2   3   4   hello\n1  5   6   7   8   world\n2  9  10  11  12     foo",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>message</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>4</td>\n      <td>hello</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>5</td>\n      <td>6</td>\n      <td>7</td>\n      <td>8</td>\n      <td>world</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>9</td>\n      <td>10</td>\n      <td>11</td>\n      <td>12</td>\n      <td>foo</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('examples/ex4.csv', skiprows=[0, 2, 3])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:16.884733Z",
     "end_time": "2024-04-18T09:37:17.036596Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "outputs": [
    {
     "data": {
      "text/plain": "  something  a   b     c   d message\n0       one  1   2   3.0   4     NaN\n1       two  5   6   NaN   8   world\n2     three  9  10  11.0  12     foo",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>something</th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>message</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>one</td>\n      <td>1</td>\n      <td>2</td>\n      <td>3.0</td>\n      <td>4</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>two</td>\n      <td>5</td>\n      <td>6</td>\n      <td>NaN</td>\n      <td>8</td>\n      <td>world</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>three</td>\n      <td>9</td>\n      <td>10</td>\n      <td>11.0</td>\n      <td>12</td>\n      <td>foo</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = pd.read_csv('examples/ex5.csv')\n",
    "result"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:16.894173Z",
     "end_time": "2024-04-18T09:37:17.052661Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "outputs": [
    {
     "data": {
      "text/plain": "   something      a      b      c      d  message\n0      False  False  False  False  False     True\n1      False  False  False   True  False    False\n2      False  False  False  False  False    False",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>something</th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>message</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>True</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>True</td>\n      <td>False</td>\n      <td>False</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.isnull(result)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:16.903954Z",
     "end_time": "2024-04-18T09:37:17.057670Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "outputs": [
    {
     "data": {
      "text/plain": "  something  a   b     c   d message\n0       one  1   2   3.0   4     NaN\n1       two  5   6   NaN   8   world\n2     three  9  10  11.0  12     foo",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>something</th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>message</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>one</td>\n      <td>1</td>\n      <td>2</td>\n      <td>3.0</td>\n      <td>4</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>two</td>\n      <td>5</td>\n      <td>6</td>\n      <td>NaN</td>\n      <td>8</td>\n      <td>world</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>three</td>\n      <td>9</td>\n      <td>10</td>\n      <td>11.0</td>\n      <td>12</td>\n      <td>foo</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = pd.read_csv('examples/ex5.csv', na_values=['NULL'])\n",
    "result"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:16.909828Z",
     "end_time": "2024-04-18T09:37:17.063293Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "outputs": [
    {
     "data": {
      "text/plain": "  something  a   b   c   d message\n0       one  1   2   3   4      NA\n1       two  5   6       8   world\n2     three  9  10  11  12     foo",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>something</th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>message</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>one</td>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>4</td>\n      <td>NA</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>two</td>\n      <td>5</td>\n      <td>6</td>\n      <td></td>\n      <td>8</td>\n      <td>world</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>three</td>\n      <td>9</td>\n      <td>10</td>\n      <td>11</td>\n      <td>12</td>\n      <td>foo</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result2 = pd.read_csv(\"examples/ex5.csv\", keep_default_na=False)\n",
    "result2"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:16.921533Z",
     "end_time": "2024-04-18T09:37:17.165038Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "outputs": [
    {
     "data": {
      "text/plain": "   something      a      b      c      d  message\n0      False  False  False  False  False    False\n1      False  False  False  False  False    False\n2      False  False  False  False  False    False",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>something</th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>message</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result2.isnull()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:16.929789Z",
     "end_time": "2024-04-18T09:37:17.165709Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "outputs": [
    {
     "data": {
      "text/plain": "  something  a   b   c   d message\n0       one  1   2   3   4     NaN\n1       two  5   6       8   world\n2     three  9  10  11  12     foo",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>something</th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>message</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>one</td>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>4</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>two</td>\n      <td>5</td>\n      <td>6</td>\n      <td></td>\n      <td>8</td>\n      <td>world</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>three</td>\n      <td>9</td>\n      <td>10</td>\n      <td>11</td>\n      <td>12</td>\n      <td>foo</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result3 = pd.read_csv(\"examples/ex5.csv\", keep_default_na=False,\n",
    "                      na_values=[\"NA\"])\n",
    "result3"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:16.938176Z",
     "end_time": "2024-04-18T09:37:17.165709Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "outputs": [
    {
     "data": {
      "text/plain": "   something      a      b      c      d  message\n0      False  False  False  False  False     True\n1      False  False  False  False  False    False\n2      False  False  False  False  False    False",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>something</th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>message</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>True</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n      <td>False</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result3.isnull()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:16.949675Z",
     "end_time": "2024-04-18T09:37:17.188197Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "outputs": [
    {
     "data": {
      "text/plain": "  something  a   b   c   d message\n0       one  1   2   3   4     NaN\n1       NaN  5   6       8   world\n2     three  9  10  11  12     NaN",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>something</th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>message</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>one</td>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>4</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>NaN</td>\n      <td>5</td>\n      <td>6</td>\n      <td></td>\n      <td>8</td>\n      <td>world</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>three</td>\n      <td>9</td>\n      <td>10</td>\n      <td>11</td>\n      <td>12</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sentinels = {\"message\": [\"foo\", \"NA\"], \"something\": [\"two\"]}\n",
    "pd.read_csv(\"examples/ex5.csv\", na_values=sentinels,\n",
    "            keep_default_na=False)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:16.956344Z",
     "end_time": "2024-04-18T09:37:17.188197Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 逐块读取文本文件"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "outputs": [
    {
     "data": {
      "text/plain": "           one       two     three      four key\n0     0.467976 -0.038649 -0.295344 -1.824726   L\n1    -0.358893  1.404453  0.704965 -0.200638   B\n2    -0.501840  0.659254 -0.421691 -0.057688   G\n3     0.204886  1.074134  1.388361 -0.982404   R\n4     0.354628 -0.133116  0.283763 -0.837063   Q\n...        ...       ...       ...       ...  ..\n9995  2.311896 -0.417070 -1.409599 -0.515821   L\n9996 -0.479893 -0.650419  0.745152 -0.646038   E\n9997  0.523331  0.787112  0.486066  1.093156   K\n9998 -0.362559  0.598894 -1.843201  0.887292   G\n9999 -0.096376 -1.012999 -0.657431 -0.573315   0\n\n[10000 rows x 5 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>one</th>\n      <th>two</th>\n      <th>three</th>\n      <th>four</th>\n      <th>key</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0.467976</td>\n      <td>-0.038649</td>\n      <td>-0.295344</td>\n      <td>-1.824726</td>\n      <td>L</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>-0.358893</td>\n      <td>1.404453</td>\n      <td>0.704965</td>\n      <td>-0.200638</td>\n      <td>B</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>-0.501840</td>\n      <td>0.659254</td>\n      <td>-0.421691</td>\n      <td>-0.057688</td>\n      <td>G</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>0.204886</td>\n      <td>1.074134</td>\n      <td>1.388361</td>\n      <td>-0.982404</td>\n      <td>R</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>0.354628</td>\n      <td>-0.133116</td>\n      <td>0.283763</td>\n      <td>-0.837063</td>\n      <td>Q</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>9995</th>\n      <td>2.311896</td>\n      <td>-0.417070</td>\n      <td>-1.409599</td>\n      <td>-0.515821</td>\n      <td>L</td>\n    </tr>\n    <tr>\n      <th>9996</th>\n      <td>-0.479893</td>\n      <td>-0.650419</td>\n      <td>0.745152</td>\n      <td>-0.646038</td>\n      <td>E</td>\n    </tr>\n    <tr>\n      <th>9997</th>\n      <td>0.523331</td>\n      <td>0.787112</td>\n      <td>0.486066</td>\n      <td>1.093156</td>\n      <td>K</td>\n    </tr>\n    <tr>\n      <th>9998</th>\n      <td>-0.362559</td>\n      <td>0.598894</td>\n      <td>-1.843201</td>\n      <td>0.887292</td>\n      <td>G</td>\n    </tr>\n    <tr>\n      <th>9999</th>\n      <td>-0.096376</td>\n      <td>-1.012999</td>\n      <td>-0.657431</td>\n      <td>-0.573315</td>\n      <td>0</td>\n    </tr>\n  </tbody>\n</table>\n<p>10000 rows × 5 columns</p>\n</div>"
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = pd.read_csv('examples/ex6.csv')\n",
    "result"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:16.965589Z",
     "end_time": "2024-04-18T09:37:17.188197Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "outputs": [
    {
     "data": {
      "text/plain": "        one       two     three      four key\n0  0.467976 -0.038649 -0.295344 -1.824726   L\n1 -0.358893  1.404453  0.704965 -0.200638   B\n2 -0.501840  0.659254 -0.421691 -0.057688   G\n3  0.204886  1.074134  1.388361 -0.982404   R\n4  0.354628 -0.133116  0.283763 -0.837063   Q",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>one</th>\n      <th>two</th>\n      <th>three</th>\n      <th>four</th>\n      <th>key</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0.467976</td>\n      <td>-0.038649</td>\n      <td>-0.295344</td>\n      <td>-1.824726</td>\n      <td>L</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>-0.358893</td>\n      <td>1.404453</td>\n      <td>0.704965</td>\n      <td>-0.200638</td>\n      <td>B</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>-0.501840</td>\n      <td>0.659254</td>\n      <td>-0.421691</td>\n      <td>-0.057688</td>\n      <td>G</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>0.204886</td>\n      <td>1.074134</td>\n      <td>1.388361</td>\n      <td>-0.982404</td>\n      <td>R</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>0.354628</td>\n      <td>-0.133116</td>\n      <td>0.283763</td>\n      <td>-0.837063</td>\n      <td>Q</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('examples/ex6.csv', nrows=5)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:16.983887Z",
     "end_time": "2024-04-18T09:37:17.215328Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "outputs": [
    {
     "data": {
      "text/plain": "pandas.io.parsers.readers.TextFileReader"
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chunker = pd.read_csv('examples/ex6.csv', chunksize=1000)\n",
    "type(chunker)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:16.996409Z",
     "end_time": "2024-04-18T09:37:17.219331Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "outputs": [
    {
     "data": {
      "text/plain": "key\nE    368\nX    364\nL    346\nO    343\nQ    340\nM    338\nJ    337\nF    335\nK    334\nH    330\ndtype: object"
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tot = pd.Series([])\n",
    "for piece in chunker:\n",
    "    tot = tot.add(piece['key'].value_counts(), fill_value=0)\n",
    "tot = tot.sort_values(ascending=False)\n",
    "tot[:10]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.002583Z",
     "end_time": "2024-04-18T09:37:17.236333Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 将数据写出到文本格式"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "outputs": [
    {
     "data": {
      "text/plain": "  something  a   b     c   d message\n0       one  1   2   3.0   4     NaN\n1       two  5   6   NaN   8   world\n2     three  9  10  11.0  12     foo",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>something</th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>message</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>one</td>\n      <td>1</td>\n      <td>2</td>\n      <td>3.0</td>\n      <td>4</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>two</td>\n      <td>5</td>\n      <td>6</td>\n      <td>NaN</td>\n      <td>8</td>\n      <td>world</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>three</td>\n      <td>9</td>\n      <td>10</td>\n      <td>11.0</td>\n      <td>12</td>\n      <td>foo</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_csv('examples/ex5.csv')\n",
    "data"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.054662Z",
     "end_time": "2024-04-18T09:37:17.236333Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "outputs": [],
   "source": [
    "data.to_csv('examples/out.csv')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.065285Z",
     "end_time": "2024-04-18T09:37:17.236333Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|something|a|b|c|d|message\r\n",
      "0|one|1|2|3.0|4|\r\n",
      "1|two|5|6||8|world\r\n",
      "2|three|9|10|11.0|12|foo\r\n"
     ]
    }
   ],
   "source": [
    "import sys\n",
    "\n",
    "data.to_csv(sys.stdout, sep='|')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.071173Z",
     "end_time": "2024-04-18T09:37:17.267339Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ",something,a,b,c,d,message\r\n",
      "0,one,1,2,3.0,4,NULL\r\n",
      "1,two,5,6,NULL,8,world\r\n",
      "2,three,9,10,11.0,12,foo\r\n"
     ]
    }
   ],
   "source": [
    "data.to_csv(sys.stdout, na_rep='NULL')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.079636Z",
     "end_time": "2024-04-18T09:37:17.301781Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "one,1,2,3.0,4,\r\n",
      "two,5,6,,8,world\r\n",
      "three,9,10,11.0,12,foo\r\n"
     ]
    }
   ],
   "source": [
    "data.to_csv(sys.stdout, index=False, header=False)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.086718Z",
     "end_time": "2024-04-18T09:37:17.334872Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a,b,c\r\n",
      "1,2,3.0\r\n",
      "5,6,\r\n",
      "9,10,11.0\r\n"
     ]
    }
   ],
   "source": [
    "data.to_csv(sys.stdout, index=False, columns=['a', 'b', 'c'])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.093807Z",
     "end_time": "2024-04-18T09:37:17.334872Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "outputs": [
    {
     "data": {
      "text/plain": "DatetimeIndex(['2000-01-01', '2000-01-02', '2000-01-03', '2000-01-04',\n               '2000-01-05', '2000-01-06', '2000-01-07'],\n              dtype='datetime64[ns]', freq='D')"
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dates = pd.date_range('1/1/2000', periods=7)\n",
    "dates"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.100451Z",
     "end_time": "2024-04-18T09:37:17.371880Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "outputs": [
    {
     "data": {
      "text/plain": "2000-01-01    0\n2000-01-02    1\n2000-01-03    2\n2000-01-04    3\n2000-01-05    4\n2000-01-06    5\n2000-01-07    6\nFreq: D, dtype: int32"
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts = pd.Series(np.arange(7), index=dates)\n",
    "ts"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.107388Z",
     "end_time": "2024-04-18T09:37:17.401284Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "outputs": [],
   "source": [
    "ts.to_csv('examples/tseries.csv')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.114221Z",
     "end_time": "2024-04-18T09:37:17.430302Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 处理分隔符格式"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['a', 'b', 'c']\n",
      "['1', '2', '3']\n",
      "['1', '2', '3']\n"
     ]
    }
   ],
   "source": [
    "import csv\n",
    "\n",
    "f = open('examples/ex7.csv')\n",
    "reader = csv.reader(f)\n",
    "for line in reader:\n",
    "    print(line)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.120995Z",
     "end_time": "2024-04-18T09:37:17.461156Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "outputs": [],
   "source": [
    "with open('examples/ex7.csv') as f:\n",
    "    lines = list(csv.reader(f))"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.125054Z",
     "end_time": "2024-04-18T09:37:17.491160Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "outputs": [],
   "source": [
    "header, values = lines[0], lines[1:]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.129390Z",
     "end_time": "2024-04-18T09:37:17.491160Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "outputs": [
    {
     "data": {
      "text/plain": "['a', 'b', 'c']"
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "header"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.134535Z",
     "end_time": "2024-04-18T09:37:17.491160Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "outputs": [
    {
     "data": {
      "text/plain": "[['1', '2', '3'], ['1', '2', '3']]"
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "values"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.139564Z",
     "end_time": "2024-04-18T09:37:17.491160Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "outputs": [
    {
     "data": {
      "text/plain": "{'a': ('1', '1'), 'b': ('2', '2'), 'c': ('3', '3')}"
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_dict = {h: v for h, v in zip(header, zip(*values))}\n",
    "data_dict"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.144807Z",
     "end_time": "2024-04-18T09:37:17.491160Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### JSON数据"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "outputs": [],
   "source": [
    "obj = \"\"\"\n",
    "{\"name\": \"Wes\",\n",
    " \"cities_lived\": [\"Akron\", \"Nashville\", \"New York\", \"San Francisco\"],\n",
    " \"pet\": null,\n",
    " \"siblings\": [{\"name\": \"Scott\", \"age\": 34, \"hobbies\": [\"guitars\", \"soccer\"]},\n",
    "              {\"name\": \"Katie\", \"age\": 42, \"hobbies\": [\"diving\", \"art\"]}]\n",
    "}\n",
    "\"\"\""
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.150798Z",
     "end_time": "2024-04-18T09:37:17.491160Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "outputs": [
    {
     "data": {
      "text/plain": "{'name': 'Wes',\n 'cities_lived': ['Akron', 'Nashville', 'New York', 'San Francisco'],\n 'pet': None,\n 'siblings': [{'name': 'Scott', 'age': 34, 'hobbies': ['guitars', 'soccer']},\n  {'name': 'Katie', 'age': 42, 'hobbies': ['diving', 'art']}]}"
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import json\n",
    "\n",
    "result = json.loads(obj)\n",
    "result"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.156575Z",
     "end_time": "2024-04-18T09:37:17.492161Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "outputs": [
    {
     "data": {
      "text/plain": "'{\"name\": \"Wes\", \"cities_lived\": [\"Akron\", \"Nashville\", \"New York\", \"San Francisco\"], \"pet\": null, \"siblings\": [{\"name\": \"Scott\", \"age\": 34, \"hobbies\": [\"guitars\", \"soccer\"]}, {\"name\": \"Katie\", \"age\": 42, \"hobbies\": [\"diving\", \"art\"]}]}'"
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "asjson = json.dumps(result)\n",
    "asjson"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.164038Z",
     "end_time": "2024-04-18T09:37:17.492161Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "outputs": [
    {
     "data": {
      "text/plain": "    name  age\n0  Scott   34\n1  Katie   42",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>name</th>\n      <th>age</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Scott</td>\n      <td>34</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Katie</td>\n      <td>42</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "siblings = pd.DataFrame(result[\"siblings\"], columns=[\"name\", \"age\"])\n",
    "siblings"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.175220Z",
     "end_time": "2024-04-18T09:37:17.492161Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "outputs": [
    {
     "data": {
      "text/plain": "   a  b  c\n0  1  2  3\n1  4  5  6\n2  7  8  9",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>4</td>\n      <td>5</td>\n      <td>6</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>7</td>\n      <td>8</td>\n      <td>9</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_json(\"examples/example.json\")\n",
    "data"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.180219Z",
     "end_time": "2024-04-18T09:37:17.813241Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\"a\":{\"0\":1,\"1\":4,\"2\":7},\"b\":{\"0\":2,\"1\":5,\"2\":8},\"c\":{\"0\":3,\"1\":6,\"2\":9}}"
     ]
    }
   ],
   "source": [
    "data.to_json(sys.stdout)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.192200Z",
     "end_time": "2024-04-18T09:37:17.845241Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{\"a\":1,\"b\":2,\"c\":3},{\"a\":4,\"b\":5,\"c\":6},{\"a\":7,\"b\":8,\"c\":9}]"
     ]
    }
   ],
   "source": [
    "data.to_json(sys.stdout, orient=\"records\")"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.195452Z",
     "end_time": "2024-04-18T09:37:17.876440Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### XML和HTML:Web信息收集"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "outputs": [
    {
     "data": {
      "text/plain": "1"
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tables = pd.read_html(\"examples/fdic_failed_bank_list.html\")\n",
    "len(tables)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.203328Z",
     "end_time": "2024-04-18T09:37:17.906446Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "outputs": [
    {
     "data": {
      "text/plain": "                      Bank Name             City  ST   CERT  \\\n0                   Allied Bank         Mulberry  AR     91   \n1  The Woodbury Banking Company         Woodbury  GA  11297   \n2        First CornerStone Bank  King of Prussia  PA  35312   \n3            Trust Company Bank          Memphis  TN   9956   \n4    North Milwaukee State Bank        Milwaukee  WI  20364   \n\n                 Acquiring Institution        Closing Date       Updated Date  \n0                         Today's Bank  September 23, 2016  November 17, 2016  \n1                          United Bank     August 19, 2016  November 17, 2016  \n2  First-Citizens Bank & Trust Company         May 6, 2016  September 6, 2016  \n3           The Bank of Fayette County      April 29, 2016  September 6, 2016  \n4  First-Citizens Bank & Trust Company      March 11, 2016      June 16, 2016  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Bank Name</th>\n      <th>City</th>\n      <th>ST</th>\n      <th>CERT</th>\n      <th>Acquiring Institution</th>\n      <th>Closing Date</th>\n      <th>Updated Date</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Allied Bank</td>\n      <td>Mulberry</td>\n      <td>AR</td>\n      <td>91</td>\n      <td>Today's Bank</td>\n      <td>September 23, 2016</td>\n      <td>November 17, 2016</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>The Woodbury Banking Company</td>\n      <td>Woodbury</td>\n      <td>GA</td>\n      <td>11297</td>\n      <td>United Bank</td>\n      <td>August 19, 2016</td>\n      <td>November 17, 2016</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>First CornerStone Bank</td>\n      <td>King of Prussia</td>\n      <td>PA</td>\n      <td>35312</td>\n      <td>First-Citizens Bank &amp; Trust Company</td>\n      <td>May 6, 2016</td>\n      <td>September 6, 2016</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Trust Company Bank</td>\n      <td>Memphis</td>\n      <td>TN</td>\n      <td>9956</td>\n      <td>The Bank of Fayette County</td>\n      <td>April 29, 2016</td>\n      <td>September 6, 2016</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>North Milwaukee State Bank</td>\n      <td>Milwaukee</td>\n      <td>WI</td>\n      <td>20364</td>\n      <td>First-Citizens Bank &amp; Trust Company</td>\n      <td>March 11, 2016</td>\n      <td>June 16, 2016</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "failures = tables[0]\n",
    "failures.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.277135Z",
     "end_time": "2024-04-18T09:37:17.956458Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "outputs": [
    {
     "data": {
      "text/plain": "Closing Date\n2010    157\n2009    140\n2011     92\n2012     51\n2008     25\n       ... \n2004      4\n2001      4\n2007      3\n2003      3\n2000      2\nName: count, Length: 15, dtype: int64"
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "close_timestamps = pd.to_datetime(failures[\"Closing Date\"])\n",
    "close_timestamps.dt.year.value_counts()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.288964Z",
     "end_time": "2024-04-18T09:37:17.956458Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 利用lxml.objectify解析XML"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "outputs": [],
   "source": [
    "from lxml import objectify\n",
    "\n",
    "path = \"datasets/mta_perf/Performance_MNR.xml\"\n",
    "with open(path) as f:\n",
    "    parsed = objectify.parse(f)\n",
    "root = parsed.getroot()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.299780Z",
     "end_time": "2024-04-18T09:37:17.983477Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "outputs": [],
   "source": [
    "data = []\n",
    "\n",
    "skip_fields = [\"PARENT_SEQ\", \"INDICATOR_SEQ\",\n",
    "               \"DESIRED_CHANGE\", \"DECIMAL_PLACES\"]\n",
    "\n",
    "for elt in root.INDICATOR:\n",
    "    el_data = {}\n",
    "    for child in elt.getchildren():\n",
    "        if child.tag in skip_fields:\n",
    "            continue\n",
    "        el_data[child.tag] = child.pyval\n",
    "    data.append(el_data)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.368881Z",
     "end_time": "2024-04-18T09:37:18.102504Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "outputs": [
    {
     "data": {
      "text/plain": "            AGENCY_NAME                        INDICATOR_NAME  \\\n0  Metro-North Railroad  On-Time Performance (West of Hudson)   \n1  Metro-North Railroad  On-Time Performance (West of Hudson)   \n2  Metro-North Railroad  On-Time Performance (West of Hudson)   \n3  Metro-North Railroad  On-Time Performance (West of Hudson)   \n4  Metro-North Railroad  On-Time Performance (West of Hudson)   \n\n                                                                  DESCRIPTION  \\\n0  Percent of commuter trains that arrive at their destinations within 5 m...   \n1  Percent of commuter trains that arrive at their destinations within 5 m...   \n2  Percent of commuter trains that arrive at their destinations within 5 m...   \n3  Percent of commuter trains that arrive at their destinations within 5 m...   \n4  Percent of commuter trains that arrive at their destinations within 5 m...   \n\n   PERIOD_YEAR  PERIOD_MONTH            CATEGORY FREQUENCY INDICATOR_UNIT  \\\n0         2008             1  Service Indicators         M              %   \n1         2008             2  Service Indicators         M              %   \n2         2008             3  Service Indicators         M              %   \n3         2008             4  Service Indicators         M              %   \n4         2008             5  Service Indicators         M              %   \n\n  YTD_TARGET YTD_ACTUAL MONTHLY_TARGET MONTHLY_ACTUAL  \n0       95.0       96.9           95.0           96.9  \n1       95.0       96.0           95.0           95.0  \n2       95.0       96.3           95.0           96.9  \n3       95.0       96.8           95.0           98.3  \n4       95.0       96.6           95.0           95.8  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>AGENCY_NAME</th>\n      <th>INDICATOR_NAME</th>\n      <th>DESCRIPTION</th>\n      <th>PERIOD_YEAR</th>\n      <th>PERIOD_MONTH</th>\n      <th>CATEGORY</th>\n      <th>FREQUENCY</th>\n      <th>INDICATOR_UNIT</th>\n      <th>YTD_TARGET</th>\n      <th>YTD_ACTUAL</th>\n      <th>MONTHLY_TARGET</th>\n      <th>MONTHLY_ACTUAL</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Metro-North Railroad</td>\n      <td>On-Time Performance (West of Hudson)</td>\n      <td>Percent of commuter trains that arrive at their destinations within 5 m...</td>\n      <td>2008</td>\n      <td>1</td>\n      <td>Service Indicators</td>\n      <td>M</td>\n      <td>%</td>\n      <td>95.0</td>\n      <td>96.9</td>\n      <td>95.0</td>\n      <td>96.9</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Metro-North Railroad</td>\n      <td>On-Time Performance (West of Hudson)</td>\n      <td>Percent of commuter trains that arrive at their destinations within 5 m...</td>\n      <td>2008</td>\n      <td>2</td>\n      <td>Service Indicators</td>\n      <td>M</td>\n      <td>%</td>\n      <td>95.0</td>\n      <td>96.0</td>\n      <td>95.0</td>\n      <td>95.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Metro-North Railroad</td>\n      <td>On-Time Performance (West of Hudson)</td>\n      <td>Percent of commuter trains that arrive at their destinations within 5 m...</td>\n      <td>2008</td>\n      <td>3</td>\n      <td>Service Indicators</td>\n      <td>M</td>\n      <td>%</td>\n      <td>95.0</td>\n      <td>96.3</td>\n      <td>95.0</td>\n      <td>96.9</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Metro-North Railroad</td>\n      <td>On-Time Performance (West of Hudson)</td>\n      <td>Percent of commuter trains that arrive at their destinations within 5 m...</td>\n      <td>2008</td>\n      <td>4</td>\n      <td>Service Indicators</td>\n      <td>M</td>\n      <td>%</td>\n      <td>95.0</td>\n      <td>96.8</td>\n      <td>95.0</td>\n      <td>98.3</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Metro-North Railroad</td>\n      <td>On-Time Performance (West of Hudson)</td>\n      <td>Percent of commuter trains that arrive at their destinations within 5 m...</td>\n      <td>2008</td>\n      <td>5</td>\n      <td>Service Indicators</td>\n      <td>M</td>\n      <td>%</td>\n      <td>95.0</td>\n      <td>96.6</td>\n      <td>95.0</td>\n      <td>95.8</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "perf = pd.DataFrame(data)\n",
    "perf.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.389832Z",
     "end_time": "2024-04-18T09:37:18.102504Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "outputs": [
    {
     "data": {
      "text/plain": "   INDICATOR_SEQ  PARENT_SEQ           AGENCY_NAME  \\\n0          28445         NaN  Metro-North Railroad   \n1          28445         NaN  Metro-North Railroad   \n2          28445         NaN  Metro-North Railroad   \n3          28445         NaN  Metro-North Railroad   \n4          28445         NaN  Metro-North Railroad   \n\n                         INDICATOR_NAME  \\\n0  On-Time Performance (West of Hudson)   \n1  On-Time Performance (West of Hudson)   \n2  On-Time Performance (West of Hudson)   \n3  On-Time Performance (West of Hudson)   \n4  On-Time Performance (West of Hudson)   \n\n                                                                  DESCRIPTION  \\\n0  Percent of commuter trains that arrive at their destinations within 5 m...   \n1  Percent of commuter trains that arrive at their destinations within 5 m...   \n2  Percent of commuter trains that arrive at their destinations within 5 m...   \n3  Percent of commuter trains that arrive at their destinations within 5 m...   \n4  Percent of commuter trains that arrive at their destinations within 5 m...   \n\n   PERIOD_YEAR  PERIOD_MONTH            CATEGORY FREQUENCY DESIRED_CHANGE  \\\n0         2008             1  Service Indicators         M              U   \n1         2008             2  Service Indicators         M              U   \n2         2008             3  Service Indicators         M              U   \n3         2008             4  Service Indicators         M              U   \n4         2008             5  Service Indicators         M              U   \n\n  INDICATOR_UNIT  DECIMAL_PLACES YTD_TARGET YTD_ACTUAL MONTHLY_TARGET  \\\n0              %               1      95.00      96.90          95.00   \n1              %               1      95.00      96.00          95.00   \n2              %               1      95.00      96.30          95.00   \n3              %               1      95.00      96.80          95.00   \n4              %               1      95.00      96.60          95.00   \n\n  MONTHLY_ACTUAL  \n0          96.90  \n1          95.00  \n2          96.90  \n3          98.30  \n4          95.80  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>INDICATOR_SEQ</th>\n      <th>PARENT_SEQ</th>\n      <th>AGENCY_NAME</th>\n      <th>INDICATOR_NAME</th>\n      <th>DESCRIPTION</th>\n      <th>PERIOD_YEAR</th>\n      <th>PERIOD_MONTH</th>\n      <th>CATEGORY</th>\n      <th>FREQUENCY</th>\n      <th>DESIRED_CHANGE</th>\n      <th>INDICATOR_UNIT</th>\n      <th>DECIMAL_PLACES</th>\n      <th>YTD_TARGET</th>\n      <th>YTD_ACTUAL</th>\n      <th>MONTHLY_TARGET</th>\n      <th>MONTHLY_ACTUAL</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>28445</td>\n      <td>NaN</td>\n      <td>Metro-North Railroad</td>\n      <td>On-Time Performance (West of Hudson)</td>\n      <td>Percent of commuter trains that arrive at their destinations within 5 m...</td>\n      <td>2008</td>\n      <td>1</td>\n      <td>Service Indicators</td>\n      <td>M</td>\n      <td>U</td>\n      <td>%</td>\n      <td>1</td>\n      <td>95.00</td>\n      <td>96.90</td>\n      <td>95.00</td>\n      <td>96.90</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>28445</td>\n      <td>NaN</td>\n      <td>Metro-North Railroad</td>\n      <td>On-Time Performance (West of Hudson)</td>\n      <td>Percent of commuter trains that arrive at their destinations within 5 m...</td>\n      <td>2008</td>\n      <td>2</td>\n      <td>Service Indicators</td>\n      <td>M</td>\n      <td>U</td>\n      <td>%</td>\n      <td>1</td>\n      <td>95.00</td>\n      <td>96.00</td>\n      <td>95.00</td>\n      <td>95.00</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>28445</td>\n      <td>NaN</td>\n      <td>Metro-North Railroad</td>\n      <td>On-Time Performance (West of Hudson)</td>\n      <td>Percent of commuter trains that arrive at their destinations within 5 m...</td>\n      <td>2008</td>\n      <td>3</td>\n      <td>Service Indicators</td>\n      <td>M</td>\n      <td>U</td>\n      <td>%</td>\n      <td>1</td>\n      <td>95.00</td>\n      <td>96.30</td>\n      <td>95.00</td>\n      <td>96.90</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>28445</td>\n      <td>NaN</td>\n      <td>Metro-North Railroad</td>\n      <td>On-Time Performance (West of Hudson)</td>\n      <td>Percent of commuter trains that arrive at their destinations within 5 m...</td>\n      <td>2008</td>\n      <td>4</td>\n      <td>Service Indicators</td>\n      <td>M</td>\n      <td>U</td>\n      <td>%</td>\n      <td>1</td>\n      <td>95.00</td>\n      <td>96.80</td>\n      <td>95.00</td>\n      <td>98.30</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>28445</td>\n      <td>NaN</td>\n      <td>Metro-North Railroad</td>\n      <td>On-Time Performance (West of Hudson)</td>\n      <td>Percent of commuter trains that arrive at their destinations within 5 m...</td>\n      <td>2008</td>\n      <td>5</td>\n      <td>Service Indicators</td>\n      <td>M</td>\n      <td>U</td>\n      <td>%</td>\n      <td>1</td>\n      <td>95.00</td>\n      <td>96.60</td>\n      <td>95.00</td>\n      <td>95.80</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "perf2 = pd.read_xml(path)\n",
    "perf2.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.400284Z",
     "end_time": "2024-04-18T09:37:18.151502Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 二进制数据格式"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "outputs": [],
   "source": [
    "frame = pd.read_csv(\"examples/ex1.csv\")\n",
    "frame.to_pickle(\"examples/frame_pickle\")"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.448662Z",
     "end_time": "2024-04-18T09:37:18.151502Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "outputs": [
    {
     "data": {
      "text/plain": "   a   b   c   d message\n0  1   2   3   4   hello\n1  5   6   7   8   world\n2  9  10  11  12     foo",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>message</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>4</td>\n      <td>hello</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>5</td>\n      <td>6</td>\n      <td>7</td>\n      <td>8</td>\n      <td>world</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>9</td>\n      <td>10</td>\n      <td>11</td>\n      <td>12</td>\n      <td>foo</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_pickle('examples/frame_pickle')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.454886Z",
     "end_time": "2024-04-18T09:37:18.151502Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 使用HDF5格式"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "outputs": [
    {
     "data": {
      "text/plain": "<class 'pandas.io.pytables.HDFStore'>\nFile path: examples/mydata.h5"
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame = pd.DataFrame({\"a\": np.random.standard_normal(100)})\n",
    "store = pd.HDFStore(\"examples/mydata.h5\")\n",
    "store[\"obj1\"] = frame\n",
    "store[\"obj1_col\"] = frame[\"a\"]\n",
    "store"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:17.462156Z",
     "end_time": "2024-04-18T09:37:19.280429Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "outputs": [
    {
     "data": {
      "text/plain": "           a\n0  -0.204708\n1   0.478943\n2  -0.519439\n3  -0.555730\n4   1.965781\n..       ...\n95  0.795253\n96  0.118110\n97 -0.748532\n98  0.584970\n99  0.152677\n\n[100 rows x 1 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>-0.204708</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>0.478943</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>-0.519439</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>-0.555730</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>1.965781</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>95</th>\n      <td>0.795253</td>\n    </tr>\n    <tr>\n      <th>96</th>\n      <td>0.118110</td>\n    </tr>\n    <tr>\n      <th>97</th>\n      <td>-0.748532</td>\n    </tr>\n    <tr>\n      <th>98</th>\n      <td>0.584970</td>\n    </tr>\n    <tr>\n      <th>99</th>\n      <td>0.152677</td>\n    </tr>\n  </tbody>\n</table>\n<p>100 rows × 1 columns</p>\n</div>"
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "store['obj1']"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:19.127635Z",
     "end_time": "2024-04-18T09:37:19.287430Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "outputs": [
    {
     "data": {
      "text/plain": "           a\n10  1.007189\n11 -1.296221\n12  0.274992\n13  0.228913\n14  1.352917\n15  0.886429",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>10</th>\n      <td>1.007189</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>-1.296221</td>\n    </tr>\n    <tr>\n      <th>12</th>\n      <td>0.274992</td>\n    </tr>\n    <tr>\n      <th>13</th>\n      <td>0.228913</td>\n    </tr>\n    <tr>\n      <th>14</th>\n      <td>1.352917</td>\n    </tr>\n    <tr>\n      <th>15</th>\n      <td>0.886429</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "store.put(\"obj2\", frame, format=\"table\")\n",
    "store.select(\"obj2\", where=[\"index >= 10 and index <= 15\"])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:19.137175Z",
     "end_time": "2024-04-18T09:37:19.287430Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "outputs": [],
   "source": [
    "store.close()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:19.156541Z",
     "end_time": "2024-04-18T09:37:19.299434Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "outputs": [
    {
     "data": {
      "text/plain": "          a\n0 -0.204708\n1  0.478943\n2 -0.519439\n3 -0.555730\n4  1.965781",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>-0.204708</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>0.478943</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>-0.519439</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>-0.555730</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>1.965781</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.to_hdf(\"examples/mydata.h5\", \"obj3\", format=\"table\")\n",
    "pd.read_hdf(\"examples/mydata.h5\", \"obj3\", where=[\"index < 5\"])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:19.162883Z",
     "end_time": "2024-04-18T09:37:19.300434Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "os.remove(\"examples/mydata.h5\")"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:19.191001Z",
     "end_time": "2024-04-18T09:37:19.300434Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 读取Microsoft Excel文件"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "outputs": [
    {
     "data": {
      "text/plain": "   Unnamed: 0  a   b   c   d message\n0           0  1   2   3   4   hello\n1           1  5   6   7   8   world\n2           2  9  10  11  12     foo",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Unnamed: 0</th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>message</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0</td>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>4</td>\n      <td>hello</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1</td>\n      <td>5</td>\n      <td>6</td>\n      <td>7</td>\n      <td>8</td>\n      <td>world</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2</td>\n      <td>9</td>\n      <td>10</td>\n      <td>11</td>\n      <td>12</td>\n      <td>foo</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xlsx = pd.ExcelFile('examples/ex1.xlsx')\n",
    "pd.read_excel(xlsx, 'Sheet1')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:19.196415Z",
     "end_time": "2024-04-18T09:37:19.539211Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "outputs": [
    {
     "data": {
      "text/plain": "   Unnamed: 0  a   b   c   d message\n0           0  1   2   3   4   hello\n1           1  5   6   7   8   world\n2           2  9  10  11  12     foo",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Unnamed: 0</th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>message</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0</td>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>4</td>\n      <td>hello</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1</td>\n      <td>5</td>\n      <td>6</td>\n      <td>7</td>\n      <td>8</td>\n      <td>world</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2</td>\n      <td>9</td>\n      <td>10</td>\n      <td>11</td>\n      <td>12</td>\n      <td>foo</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame = pd.read_excel('examples/ex1.xlsx', 'Sheet1')\n",
    "frame"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:19.416183Z",
     "end_time": "2024-04-18T09:37:19.588858Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "outputs": [],
   "source": [
    "frame.to_excel('examples/ex2.xlsx')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:19.431668Z",
     "end_time": "2024-04-18T09:37:19.592854Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 6.3 Web Apis交互"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "outputs": [
    {
     "data": {
      "text/plain": "<Response [200]>"
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import requests\n",
    "\n",
    "url = \"https://api.github.com/repos/pandas-dev/pandas/issues\"\n",
    "resp = requests.get(url)\n",
    "resp.raise_for_status()\n",
    "resp"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:19.449190Z",
     "end_time": "2024-04-18T09:37:20.897204Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "outputs": [
    {
     "data": {
      "text/plain": "'BUG: Setting values on DataFrame with multi-index .loc(axis=0) access adds columns'"
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = resp.json()\n",
    "data[0]['title']"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:20.896199Z",
     "end_time": "2024-04-18T09:37:20.901537Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 6.4 数据库交互"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "query = \"\"\"\n",
    "CREATE TABLE test\n",
    "(a VARCHAR(20), b VARCHAR(20),\n",
    " c REAL,        d INTEGER\n",
    ");\"\"\"\n",
    "\n",
    "con = sqlite3.connect(\"mydata.sqlite\")\n",
    "con.execute(query)\n",
    "con.commit()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:20.904538Z",
     "end_time": "2024-04-18T09:37:20.913241Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "outputs": [],
   "source": [
    "data = [(\"Atlanta\", \"Georgia\", 1.25, 6),\n",
    "        (\"Tallahassee\", \"Florida\", 2.6, 3),\n",
    "        (\"Sacramento\", \"California\", 1.7, 5)]\n",
    "stmt = \"INSERT INTO test VALUES(?, ?, ?, ?)\"\n",
    "\n",
    "con.executemany(stmt, data)\n",
    "con.commit()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:20.915243Z",
     "end_time": "2024-04-18T09:37:20.922669Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "outputs": [
    {
     "data": {
      "text/plain": "[('Atlanta', 'Georgia', 1.25, 6),\n ('Tallahassee', 'Florida', 2.6, 3),\n ('Sacramento', 'California', 1.7, 5)]"
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor = con.execute(\"SELECT * FROM test\")\n",
    "rows = cursor.fetchall()\n",
    "rows"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:20.921667Z",
     "end_time": "2024-04-18T09:37:20.992910Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "outputs": [
    {
     "data": {
      "text/plain": "(('a', None, None, None, None, None, None),\n ('b', None, None, None, None, None, None),\n ('c', None, None, None, None, None, None),\n ('d', None, None, None, None, None, None))"
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.description"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:20.927547Z",
     "end_time": "2024-04-18T09:37:20.992910Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "outputs": [
    {
     "data": {
      "text/plain": "             a           b     c  d\n0      Atlanta     Georgia  1.25  6\n1  Tallahassee     Florida  2.60  3\n2   Sacramento  California  1.70  5",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Atlanta</td>\n      <td>Georgia</td>\n      <td>1.25</td>\n      <td>6</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Tallahassee</td>\n      <td>Florida</td>\n      <td>2.60</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Sacramento</td>\n      <td>California</td>\n      <td>1.70</td>\n      <td>5</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(rows, columns=[x[0] for x in cursor.description])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:20.932907Z",
     "end_time": "2024-04-18T09:37:20.993912Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "outputs": [
    {
     "data": {
      "text/plain": "             a           b     c  d\n0      Atlanta     Georgia  1.25  6\n1  Tallahassee     Florida  2.60  3\n2   Sacramento  California  1.70  5",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Atlanta</td>\n      <td>Georgia</td>\n      <td>1.25</td>\n      <td>6</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Tallahassee</td>\n      <td>Florida</td>\n      <td>2.60</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Sacramento</td>\n      <td>California</td>\n      <td>1.70</td>\n      <td>5</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlalchemy as sqla\n",
    "\n",
    "db = sqla.create_engine(\"sqlite:///mydata.sqlite\")\n",
    "pd.read_sql(\"SELECT * FROM test\", db)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:20.942900Z",
     "end_time": "2024-04-18T09:37:21.139592Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-04-18T09:37:21.101218Z",
     "end_time": "2024-04-18T09:37:21.139592Z"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
